using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

namespace Destra.Tools.DACGenerator.Formatters
{
    public class SqlFormatter
    {

        #region Constructors

        public SqlFormatter()
        {
        }

        public SqlFormatter(string sql)
        {
            SqlText = sql;
        }

        #endregion

        private string sqlText;
        private string innerSqlText;

        public string SqlText
        {
            get { return sqlText; }
            set
            {
                sqlText = value;

                // remove all DBO references (we'll add them back in later on)
                sqlText = sqlText.Replace(" [dbo].", " ");
                sqlText = sqlText.Replace(" dbo.", " ");
                sqlText = sqlText.Replace(" [DBO].", " ");
                sqlText = sqlText.Replace(" DBO.", " ");
                sqlText = sqlText.Replace(Environment.NewLine + "[dbo].", " ");
                sqlText = sqlText.Replace(Environment.NewLine + "dbo.", " ");
                sqlText = sqlText.Replace(Environment.NewLine + "[DBO].", " ");
                sqlText = sqlText.Replace(Environment.NewLine + "DBO.", " ");

                innerSqlText = sqlText;
            }
        }

        public string Format()
        {
            Dictionary<string, string> sqlStrings = new Dictionary<string, string>();

            // put all strings '' into a dictionary
            innerSqlText = " " + this.HideSqlStrings(innerSqlText, sqlStrings);

            innerSqlText = this.RemoveDoubleSpaces(innerSqlText);
            innerSqlText = this.RemoveDoubleLineBreaks(innerSqlText);
            innerSqlText = this.FormatReservedWordCase(innerSqlText);
            innerSqlText = this.FormatTableNames(innerSqlText);
            innerSqlText = this.FormatLineBreaks(innerSqlText);
            innerSqlText = this.DisplaySqlStrings(innerSqlText, sqlStrings);
            
            // Kane this is pascal casing the parameters in the update (and probably insert). This is a problem as the entity uses different casing.
            // Perhaps you'll need to differentiate between input parameters and local vars (local vars appear after a declare statement?)
            //innerSqlText = this.FormatSqlVariables(innerSqlText);

            return innerSqlText;
        }

        #region Sql Reserved Words

        private string[] NewLineWordsBefore()
        {
            return new string[] { "SELECT", "FROM", "JOIN", "INNER JOIN", "LEFT OUTER JOIN", "LEFT JOIN", "RIGHT OUTER JOIN", "RIGHT JOIN", "WHERE", "INSERT INTO", "ORDER BY", "GROUP BY", "HAVING", "VALUES", "SET", "END"};
        }

        private string[] NewLineWordsAfter()
        {
            return new string[] { " AND", " OR ", ", ", " BEGIN", "ELSE", " END"};
        }

        private string[] NewLineWordsAfterThatShouldBeFixed()
        {
            return new string[] { " ISNULL", " COALESCE", "BETWEEN" };
        }

        private string[] ReservedWords()
        {

            return new string[] {   "ABS",
                                        "ADD",
                                        "ALL",
                                        "ALTER",
                                        "AND",
                                        "ANY",
                                        "AS",
                                        "ASC",
                                        "AUTOINCREMENT",
                                        "AVA",
                                        "BETWEEN",
                                        "BINARY",
                                        "BIT",
                                        "BOOLEAN",
                                        "BY CREATE",
                                        "BYTE",
                                        "CAST",
                                        "CHAR",
                                        "CHARACTER",
                                        "COLUMN",
                                        "CONSTRAINT",
                                        "COUNT",
                                        "COUNTER",
                                        "CURRENCY",
                                        "DATABASE",
                                        "DATE",
                                        "DATETIME",
                                        "DELETE",
                                        "DESC",
                                        "DISALLOW",
                                        "DISTINCT",
                                        "DISTINCTROW",
                                        "DOUBLE",
                                        "DROP",
                                        "EXISTS",
                                        "FROM",
                                        "FLOAT",
                                        "FLOAT4",
                                        "FLOAT8",
                                        "FOREIGN",
                                        "GENERAL",
                                        "GROUP BY",
                                        "GUID",
                                        "HAVING",
                                        "INNER",
                                        "INSERT",
                                        "IGNORE",
                                        "IMP",
                                        "IN",
                                        "INDEX",
                                        "INT",
                                        "INTEGER",
                                        "INTEGER1",
                                        "INTEGER2",
                                        "INTEGER4",
                                        "INTO",
                                        "ISNULL",
                                        "IS",
                                        "JOIN",
                                        "KEY",
                                        "LEFT",
                                        "LEVEL",
                                        "LIKE",
                                        "LOGICAL",
                                        "LONG",
                                        "LONGBINARY",
                                        "LONGTEXT",
                                        "MAX",
                                        "MEMO",
                                        "MIN",
                                        "MOD",
                                        "MONEY",
                                        "NOT",
                                        "NULL",
                                        "NUMBER",
                                        "NUMERIC",
                                        "OLEOBJECT",
                                        "ON PIVOT",
                                        "ON",
                                        "OPTION PRIMARY",
                                        "ORDER",
                                        "OR",
                                        "OUTER",
                                        "OWNERACCESS",
                                        "OVER",
                                        "PARAMETERS",
                                        "PERCENT",
                                        "REAL",
                                        "REFERENCES",
                                        "RIGHT",
                                        "ROW_NUMBER()",
                                        "SELECT",
                                        "SET",
                                        "SHORT",
                                        "SINGLE",
                                        "SMALLINT",
                                        "SOME",
                                        "STDEV",
                                        "STDEVP",
                                        "STRING",
                                        "SUM",
                                        "TABLE",
                                        "TABLEID",
                                        "TEXT",
                                        "TIME",
                                        "TIMESTAMP",
                                        "TOP",
                                        "TRANSFORM",
                                        "UNION",
                                        "UNIQUE",
                                        "UPDATE",
                                        "VALUE",
                                        "VALUES",
                                        "VAR",
                                        "VARBINARY",
                                        "VARCHAR",
                                        "VARP",
                                        "WHERE",
                                        "WITH",
                                        "YESNO"};
        }

        #endregion

        #region Core Methods

        private string TabLevel(string sql, int level)
        {
            for (int i = 0; i < level; i++)
            {
                sql = "\t" + sql;
            }
            return sql;
        }

        private string RemoveDoubleSpaces(string sql)
        {
            sql = sql.Replace(",", " , ");
            sql = sql.Replace("+", " + ");
            sql = sql.Replace("/", " / ");
            sql = sql.Replace("=", " = ");
            sql = sql.Replace("*", " * ");
            sql = sql.Replace(">", " > ");
            sql = sql.Replace("<", " < ");
            sql = sql.Replace("-", " - ");
            sql = sql.Replace(" -  - ", "--");

            sql = sql.Replace("\t", "");

            // remove any double spaces
            while (sql.Contains("  "))
            {
                sql = sql.Replace("  ", " ");
            }
            return sql;
        }

        private string RemoveDoubleLineBreaks(string sql)
        {
            StringBuilder lineRemoverBuilder = new StringBuilder();
            foreach (string line in Lines(sql))
            {
                if (!string.IsNullOrEmpty(line) && !string.IsNullOrEmpty(line.Trim()))
                {
                    lineRemoverBuilder.AppendLine(line);
                }
            }

            return lineRemoverBuilder.ToString();
        }

        private string[] Lines(string sql)
        {
            List<string> linesList = new List<string>();

            char[] lines = new char[] { Convert.ToChar("\n"), Convert.ToChar("\r") };
            foreach (string line in sql.Split(lines))
            {
                string innerLine = line;
                if (!string.IsNullOrEmpty(innerLine))
                {
                    linesList.Add(innerLine);
                }
            }
            return linesList.ToArray();
        }

        private int CountOpenBrackets(string sql)
        {
            Regex brackets = new Regex(@"\(");
            return brackets.Matches(sql).Count;
        }

        private int CountCloseBrackets(string sql)
        {
            Regex brackets = new Regex(@"\)");
            return brackets.Matches(sql).Count;
        }

        private string CamelCase(string input)
        {
            if (input.StartsWith("@"))
            {
                return "@" + input.Substring(1, 1).ToLowerInvariant() + input.Substring(2);
            }
            return input.Substring(0, 1).ToLowerInvariant() + input.Substring(1);
        }

        #endregion

        #region Table Related Methods

        private string FormatTableNames(string sql)
        {
            sql = sql.Replace("[", "").Replace("]", "");
            foreach (string tableName in TableNames())
            {
                sql = sql.Replace(" " + tableName + " ", " [dbo].[" + tableName + "] ");
                sql = sql.Replace(" " + tableName + Environment.NewLine, " [dbo].[" + tableName + "]" + Environment.NewLine);
            }
            return sql;
        }


        private List<string> ParseTableName(string sourceType)
        {
            int startIndex;
            string tableName;

            List<string> names = new List<string>();

            // HMM need something for sub queries as inner joins

            // set our reserved words case
            string parsedSql = this.FormatReservedWordCase(SqlText);
            parsedSql = parsedSql.Replace(Environment.NewLine, " ");

            bool contine = parsedSql.Contains(" " + sourceType.ToUpperInvariant() + " ");
            while (contine)
            {
                startIndex = parsedSql.IndexOf(" " + sourceType.ToUpperInvariant() + " ");
                if (startIndex.Equals(-1))
                {
                    break;
                }
                parsedSql = parsedSql.Substring(startIndex);
                parsedSql = parsedSql.Substring(6);

                if (!parsedSql.Replace(" ", "").StartsWith("("))
                {
                    tableName = parsedSql.Substring(0, parsedSql.IndexOf(" ")).Replace("[", "").Replace("]", "");
                    if (!string.IsNullOrEmpty(tableName) && !names.Contains(tableName))
                    {
                        names.Add(tableName);
                    }
                }
            }
            return names;
        }

        // get the table names
        List<string> tableNames = null;
        private List<string> TableNames()
        {
            if (tableNames == null)
            {
                tableNames = new List<string>();
                tableNames.AddRange(ParseTableName("FROM"));
                tableNames.AddRange(ParseTableName("JOIN"));
                tableNames.AddRange(ParseTableName("UPDATE"));
                tableNames.AddRange(ParseTableName("INTO"));
            }
            return tableNames;
        }

        #endregion

        #region TSql String Methods

        private string HideSqlStrings(string sql, Dictionary<string, string> sqlStrings)
        {
            sql = sql + " ";
            int startIndex;
            bool contine = sql.Contains("'");
            while (contine)
            {
                startIndex = sql.IndexOf("'");
                if (startIndex.Equals(-1))
                {
                    break;
                }

                Guid sqlConstant = Guid.NewGuid();
                string sqlVariable = sql.Substring(startIndex + 1);
                if (!sqlVariable.IndexOf("'").Equals(-1))
                {
                    sqlVariable = sqlVariable.Substring(0, sqlVariable.IndexOf("'"));

                    sqlStrings.Add(sqlConstant.ToString().Replace("-", ""), sqlVariable);
                    sql = sql.Replace(string.Format("'{0}'", sqlVariable), sqlConstant.ToString().Replace("-", ""));
                }
                else
                {
                    sql = sql.Substring(0, startIndex) + "`" + sql.Substring(startIndex + 1);
                }
            }

            return sql;
        }

        private string DisplaySqlStrings(string sql, Dictionary<string, string> sqlStrings)
        {
            foreach (string key in sqlStrings.Keys)
            {
                sql = sql.Replace(key, string.Format("'{0}'", sqlStrings[key]));
            }

            return sql.Trim();
        }

        #endregion

        #region TSql Formatting

        private string FormatLineBreaks(string sql)
        {
            StringBuilder builder = new StringBuilder();
            foreach (string line in this.Lines(sql))
            {
                string innerLine = line;
                if (!innerLine.StartsWith(" "))
                {
                    innerLine = " " + innerLine;
                }

                if (innerLine.StartsWith(" --"))
                {
                    innerLine = Environment.NewLine + innerLine + Environment.NewLine;
                }
                else
                {
                    foreach (string reservedWord in NewLineWordsBefore())
                    {
                        if (reservedWord.Equals("JOIN") && (line.Contains("INNER JOIN") || line.Contains("LEFT JOIN") || line.Contains("RIGHT JOIN") || line.Contains("RIGHT OUTER JOIN") || line.Contains("LEFT OUTER JOIN")))
                        {

                        }
                        else
                        {
                            innerLine = innerLine.Replace(reservedWord, Environment.NewLine + reservedWord);
                        }
                    }

                    foreach (string reservedWord in NewLineWordsAfter())
                    {
                        bool addNewLine = true;
                        foreach (string specialWord in NewLineWordsAfterThatShouldBeFixed())
                        {
                            if (innerLine.Contains(specialWord))
                            {
                                if (!specialWord.Equals("BETWEEN"))
                                {
                                    innerLine = innerLine.Replace(specialWord, Environment.NewLine + specialWord);
                                }
                                addNewLine = false;
                                break;
                            }
                        }

                        if (addNewLine)
                        {
                            innerLine = innerLine.Replace(reservedWord, reservedWord + Environment.NewLine);
                        }
                    }
                }
                builder.Append(innerLine);
            }

            // remove any starting or trailing spaces
            sql = builder.ToString();
            builder = new StringBuilder();
            foreach (string line in this.Lines(sql))
            {
                builder.AppendLine(line.Trim().Replace("  ", " "));
            }

            // now try and build the indent level
            int indent = 0;
            sql = builder.ToString();
            builder = new StringBuilder();
            foreach (string line in this.Lines(this.RemoveDoubleLineBreaks(sql)))
            {
                if (!line.StartsWith("--"))
                {
                    if (line.Trim().Equals("("))
                    {
                        indent += 1;
                    }
                    if (line.Contains(" JOIN "))
                    {
                        indent += 1;
                    }
                    if (line.Contains("FROM "))
                    {
                        indent -= 1;
                    }
                }

                builder.AppendLine(TabLevel(line, indent));

                if (!line.StartsWith("--"))
                {
                    if (line.Contains("SELECT "))
                    {
                        indent += 1;
                    }
                    if (!line.Trim().Equals("(") && this.CountOpenBrackets(line) > this.CountCloseBrackets(line))
                    {
                        indent += 1;
                    }
                    if (this.CountCloseBrackets(line) > this.CountOpenBrackets(line))
                    {
                        indent -= 1;
                    }
                    if (line.Contains(" JOIN "))
                    {
                        indent -= 1;
                    }
                }
            }

            return this.RemoveDoubleLineBreaks(builder.ToString());
        }

        private string FormatReservedWordCase(string sql)
        {
            // convert our specific words to a statement (on each line, as we don't care about comments)
            StringBuilder reservedWordBuilder = new StringBuilder();
            string innerLine = "";

            foreach (string line in this.Lines(sql))
            {
                innerLine = line;
                if (!innerLine.StartsWith(" "))
                {
                    innerLine = " " + innerLine;
                }
                if (!innerLine.EndsWith(" "))
                {
                    innerLine = innerLine + " ";
                }

                foreach (string reservedWord in ReservedWords())
                {
                    if (!innerLine.StartsWith(" --"))
                    {
                        innerLine = innerLine.Replace(" " + reservedWord.ToLowerInvariant() + " ", " " + reservedWord + " ");
                        innerLine = innerLine.Replace(" " + reservedWord.ToLowerInvariant() + "(", " " + reservedWord + "(");
                        innerLine = innerLine.Replace(" (" + reservedWord.ToLowerInvariant() + " ", " (" + reservedWord + " ");
                        innerLine = innerLine.Replace("(" + reservedWord.ToLowerInvariant() + "(", "(" + reservedWord + "(");
                        innerLine = innerLine.Replace("(" + reservedWord.ToLowerInvariant() + "( ", "(" + reservedWord + "( ");

                    }
                }

                reservedWordBuilder.AppendLine(innerLine);
            }

            sql = reservedWordBuilder.ToString();

            sql = RemoveDoubleSpaces(sql);
            sql = RemoveDoubleLineBreaks(sql);
            return sql;
        }

        #endregion

        #region TSql Variable Related Methods

        private string FormatSqlVariables(string sql)
        {
            foreach (Match match in Regex.Matches(sql, @"@[a-zA-Z0-9]*"))
            {
                sql = sql.Replace(match.Value, CamelCase(match.Value));
            }

            return sql;
        }

        #endregion

    }
}
